1. Field of the Invention
This invention relates in general to computer-implemented database systems, and, in particular, to a two-level caching system for prepared Structured Query Language statements in a relational database management system.
2. Description of Related Art
Databases are computerized information storage and retrieval systems. A Relational Database Management System (RDBMS) is a database management system (DBMS) which uses relational techniques for storing and retrieving data. Relational databases are organized into tables which consist of rows and columns of data. The rows are formally called tuples. A database will typically have many tables and each table will typically have multiple tuples and multiple columns. The tables are typically stored on direct access storage devices (DASD) such as magnetic or optical disk drives for semi-permanent storage.
RDBMS software using a Structured Query Language (SQL) interface is well known in the art. The SQL interface has evolved into a standard language for RDBMS software and has been adopted as such by both the American National Standards Institute (ANSI) and the International Standards Organization (ISO). The SQL interface allows users to formulate relational operations on the tables either interactively, in batch files, or embedded in host languages, such as C and COBOL. SQL allows the user to manipulate the data.
The definitions for SQL provide that the RDBMS software should respond to a particular query with a particular set of data given a specified database content, but the method that the RDBMS software uses to actually find the required information in the tables on the disk drives is left up to the RDBMS software. Typically, there will be more than one method that can be used by the RDBMS software to access the required data. The RDBMS software will optimize the method used to find the data requested in a query in order to minimize the computer time used and, therefore, the cost of performing the query.
Relational database management systems that interact with application programs using dynamic SQL have some inherent problems in achieving the highest levels of performance and concurrence. The dynamic SQL model requires that an application program first present an SQL statement to the RDBMS software to be "prepared". The RDBMS software validates the SQL statement, chooses an access plan for execution, and builds an executable form of the SQL statement. When the prepare request is completed, the application program can then execute the SQL statement. The preparation process often requires much more processing than the actual execution. When the application program later performs a commit operation, the prepared SQL statement is normally discarded. If the application program wishes to execute the SQL statement again, the application program must repeat the prepare request before executing the SQL statement.
Some relational database systems have helped to resolve this problem by using a "global" cache area to save the executable structures of the most recently executed SQL statements. If an application program requests a prepare for a SQL statement that has been globally cached, the entire preparation process can be skipped by obtaining a copy of the executable structures from the global cache. The global cache is generally managed with a least-recently used (LRU) technique to keep the structures for SQL statements used most recently while discarding the structures for the oldest SQL statements.
The global cache approach, however, requires substantial processing costs involved in searching the global cache, making a copy of the executable structures, and managing the LRU technique for optimizing the global cache space. Moreover, the global cache approach is inefficient in that an application program that prepares and executes a SQL statement prior to and after a commit point must release all locks needed for execution of the SQL statement at the commit point, and then re-acquire those locks to execute the SQL statement again. There is a need in the art for an improved method of caching SQL statements.